'''
Created on 2022/08/10

@author: zz
'''
import sys, os, re
import xlrd
import openpyxl
from openpyxl.utils import get_column_letter, column_index_from_string
from openpyxl.styles import PatternFill
import configparser

ini_file = '158-msd-v3.00-check.ini'
Color = ['ffff00', '00ccff']  # 黄、浅蓝
fille = PatternFill('solid', fgColor=Color[0])  # 设置填充颜色为 黄色
fille1 = PatternFill('solid', fgColor=Color[1])  # 设置填充颜色为 黄色

def write_excel_xlsx(path, sheet_name, value):
    index = len(value)
    workbook = openpyxl.Workbook()
    sheet = workbook.create_sheet("sheet1")
    sheet = workbook.active
    # sheet = workbook.create_sheet(sheet_name)
    sheet.title = sheet_name
    for i in range(0, index):
        for j in range(0, len(value[i])):
            if i == 0:
                sheet.cell(row=i+1, column=j+1, value=str(value[i][j])).fill = fille1
            else:    
                if str(value[i][j]).find("->") != -1 or value[i][j] == "Y" or value[i][j] == "SPEC1":
                    sheet.cell(row=i+1, column=j+1, value=str(value[i][j])).fill = fille
                else:
                    sheet.cell(row=i+1, column=j+1, value=str(value[i][j]))
    workbook.save(path)
    print("创建xlsx格式表格写入数据成功!")

def addExcel(path, sheet, value):
    '''
	:param sheet:sheet的名称
    :param path:写入excel的路径
    :param value: 追加的数据
    :return:
    '''
    index = len(value)
    wb = openpyxl.load_workbook(path)
    wb.create_sheet(sheet)
    ws = wb[sheet]
    # for ss in value:
    #     ws.append(ss)
    for i in range(0, index):
        for j in range(0, len(value[i])):
            if i == 0:
                ws.cell(row=i+1, column=j+1, value=str(value[i][j])).fill = fille1
            else:
                if str(value[i][j]).find("->") != -1 or value[i][j] == "Y" or value[i][j] == "SPEC1":
                    ws.cell(row=i+1, column=j+1, value=str(value[i][j])).fill = fille
                else:
                    ws.cell(row=i+1, column=j+1, value=str(value[i][j]))
    wb.save(path)
    print("xlsx格式新增sheet写入文件成功!")

def deleteExcel(path, sheet):
    delete_flag = False
    wb = openpyxl.load_workbook(path)
    for ws in wb.worksheets:
        #指定シートが存在していれば、変数にTrueを格納
        if ws.title == sheet:
            del wb[sheet]
            delete_flag = True
    if delete_flag:
        wb.save(path)
        print("xlsx格式删除sheet成功!")

def read_excel_xlsx(path, sheet_name, ba_columns, ba_start_row, value):
    workbook = openpyxl.load_workbook(path)
    # sheet = wb.get_sheet_by_name(sheet_name)这种方式已经弃用，不建议使用
    sheet = workbook[sheet_name]
    print('获取工作表的大小:')
    print('总行数 -> ' + str(sheet.max_row))
    print('总列数 -> ' + str(sheet.max_column))

    columns=[""]*20
    for i in range(1,int(ba_start_row)):
        for j in range(1,sheet.max_column):
            if sheet.cell(row=i, column=j).value == ba_columns.split(",")[0].replace("\\n","\n"):
                columns[0] = get_column_letter(j)
            if sheet.cell(row=i, column=j).value == ba_columns.split(",")[1].replace("\\n","\n"):
                columns[1] = get_column_letter(j)
            if sheet.cell(row=i, column=j).value == ba_columns.split(",")[2].replace("\\n","\n"):
                columns[2] = get_column_letter(j)
            if sheet.cell(row=i, column=j).value == ba_columns.split(",")[3].replace("\\n","\n"):
                columns[3] = get_column_letter(j)
            if sheet.cell(row=i, column=j).value == ba_columns.split(",")[4].replace("\\n","\n"):
                columns[4] = get_column_letter(j)
            if sheet.cell(row=i, column=j).value == ba_columns.split(",")[5].replace("\\n","\n"):
                columns[5] = get_column_letter(j)
            if sheet.cell(row=i, column=j).value == ba_columns.split(",")[6].replace("\\n","\n"):
                columns[6] = get_column_letter(j)
            if sheet.cell(row=i, column=j).value == ba_columns.split(",")[7].replace("\\n","\n"):
                columns[7] = get_column_letter(j)
            if sheet.cell(row=i, column=j).value == ba_columns.split(",")[8].replace("\\n","\n"):
                columns[8] = get_column_letter(j)
            if sheet.cell(row=i, column=j).value == ba_columns.split(",")[9].replace("\\n","\n"):
                columns[9] = get_column_letter(j)
            if sheet.cell(row=i, column=j).value == ba_columns.split(",")[10].replace("\\n","\n"):
                columns[10] = get_column_letter(j)
    print(ba_columns.split(","))
    print (columns)
    # for row in sheet.rows:
    #     for cell in row:
    #         print(cell.value, "\t", end="")
    start_num = int(ba_start_row)
    for i in range(start_num,sheet.max_row+1):
        j=i-start_num
        value[j][0]=sheet.cell(row=i, column=column_index_from_string(columns[0])).value
        value[j][1]=sheet.cell(row=i, column=column_index_from_string(columns[1])).value
        value[j][2]=sheet.cell(row=i, column=column_index_from_string(columns[2])).value
        value[j][3]=sheet.cell(row=i, column=column_index_from_string(columns[3])).value
        value[j][4]=sheet.cell(row=i, column=column_index_from_string(columns[4])).value
        value[j][5]=sheet.cell(row=i, column=column_index_from_string(columns[5])).value
        value[j][6]=sheet.cell(row=i, column=column_index_from_string(columns[6])).value
        value[j][7]=sheet.cell(row=i, column=column_index_from_string(columns[7])).value
        value[j][8]=sheet.cell(row=i, column=column_index_from_string(columns[8])).value
        value[j][9]=sheet.cell(row=i, column=column_index_from_string(columns[9])).value
        value[j][10]=sheet.cell(row=i, column=column_index_from_string(columns[10])).value

        if value[j][1] is None:
            value[j][1] = value[j-1][1]
        if value[j][9] is None:
            value[j][9] = ""
        if value[j][10] is None:
            value[j][10] = ""


def read_date_excel_xls(path, sheet_name, ba_columns, ba_start_row, value):
    print(ba_columns.split(","))
    workbook = xlrd.open_workbook(path)
    # sheet = wb.get_sheet_by_name(sheet_name)这种方式已经弃用，不建议使用
    sheet = workbook.sheet_by_name(sheet_name)
    print('获取工作表的大小:')
    print('总行数 -> ' + str(sheet.nrows))
    print('总列数 -> ' + str(sheet.ncols))

    tmp_value=ba_columns.split(",")[8].replace("\\n","\n")+","+ba_columns.split(",")[9].replace("\\n","\n")+","+ba_columns.split(",")[10].replace("\\n","\n")
    start_num = int(ba_start_row)
    for i in range(start_num-1,sheet.nrows):
        for j in range(0,sheet.ncols):
            col_num=j+4
            # print (str(i) +":" + str(j) +":"+sheet.cell_value(i, j))
            if sheet.cell(i, j).value == ba_columns.split(",")[0].replace("\\n","\n"):
                value[0]=sheet.cell(i, j+4).value
            if sheet.cell(i, j).value == ba_columns.split(",")[1].replace("\\n","\n"):
                value[1]=sheet.cell(i, j+4).value
            if sheet.cell(i, j).value == ba_columns.split(",")[2].replace("\\n","\n"):
                value[2]=str(sheet.cell(i, j+4).value)
            if sheet.cell(i, j).value == ba_columns.split(",")[3].replace("\\n","\n"):
                value[3]=sheet.cell(i, j+4).value
            if sheet.cell(i, j).value == ba_columns.split(",")[4].replace("\\n","\n"):
                value[4]=sheet.cell(i, j+4).value
            if sheet.cell(i, j).value == ba_columns.split(",")[5].replace("\\n","\n"):
                value[5]=sheet.cell(i, j+4).value
            if sheet.cell(i, j).value == ba_columns.split(",")[6].replace("\\n","\n"):
                value[6]=sheet.cell(i, j+4).value
            if sheet.cell(i, j).value == ba_columns.split(",")[7].replace("\\n","\n"):
                value[7]=sheet.cell(i+1, j+4).value
            if str(sheet.cell(i, j).value).find(tmp_value) != -1:
                value[8]=sheet.cell(i, j+2).value
                value[9]=sheet.cell(i+3, j+2).value
            
    print (value)

def read_date_excel_xlsx(path, sheet_name, ba_columns, ba_start_row, value):
    workbook = openpyxl.load_workbook(path)
    # sheet = wb.get_sheet_by_name(sheet_name)这种方式已经弃用，不建议使用
    sheet = workbook[sheet_name]
    print('获取工作表的大小:')
    print('总行数 -> ' + str(sheet.max_row))
    print('总列数 -> ' + str(sheet.max_column))

    tmp_value=ba_columns.split(",")[8].replace("\\n","\n")+","+ba_columns.split(",")[9].replace("\\n","\n")+","+ba_columns.split(",")[10].replace("\\n","\n")
    start_num = int(ba_start_row)
    for i in range(start_num,sheet.max_row):
        for j in range(1,sheet.max_column):
            if sheet.cell(row=i, column=j).value == ba_columns.split(",")[0].replace("\\n","\n"):
                value[0]=sheet.cell(row=i, column=j+4).value
            if sheet.cell(row=i, column=j).value == ba_columns.split(",")[1].replace("\\n","\n"):
                value[1]=sheet.cell(row=i, column=j+4).value
            if sheet.cell(row=i, column=j).value == ba_columns.split(",")[2].replace("\\n","\n"):
                value[2]=sheet.cell(row=i, column=j+4).value
            if sheet.cell(row=i, column=j).value == ba_columns.split(",")[3].replace("\\n","\n"):
                value[3]=sheet.cell(row=i, column=j+4).value
            if sheet.cell(row=i, column=j).value == ba_columns.split(",")[4].replace("\\n","\n"):
                value[4]=sheet.cell(row=i, column=j+4).value
            if sheet.cell(row=i, column=j).value == ba_columns.split(",")[5].replace("\\n","\n"):
                value[5]=sheet.cell(row=i, column=j+4).value
            if sheet.cell(row=i, column=j).value == ba_columns.split(",")[6].replace("\\n","\n"):
                value[6]=sheet.cell(row=i, column=j+4).value
            if sheet.cell(row=i, column=j).value == ba_columns.split(",")[7].replace("\\n","\n"):
                value[7]=sheet.cell(row=i+1, column=j+4).value
            if str(sheet.cell(row=i, column=j).value).find(tmp_value) != -1:
                value[8]=sheet.cell(row=i, column=j+2).value
                value[9]=sheet.cell(row=i+3, column=j+2).value




if __name__ == '__main__':

    # BASE_DIR = os.path.dirname(os.path.abspath(__file__))
    BASE_DIR = os.path.abspath("./")
    # BASE_DIR = os.getcwd()
    print(BASE_DIR)
    # 创建配置文件对象
    con = configparser.ConfigParser()

    # 读取文件
    con.read(os.path.join(BASE_DIR,ini_file), encoding='utf-8')
    print(os.path.join(BASE_DIR,ini_file))

    input_file = con.get('file','input_file')
    input_folder = con.get('file','input_folder')
    date_sheet = con.get('file','date_sheet')
    file_sheet = con.get('file','file_sheet')
    xlsx_sheet = con.get('file','xlsx_sheet')
    file_columns = con.get('file','file_columns')
    date_columns = con.get('file','date_columns')
    file_start_row = con.get('file','file_start_row')
    date_start_row = con.get('file','date_start_row')
    output_file = con.get('file','output_file')

    if not output_file.endswith(".xlsx"):
        print(f'{output_file} is not a Excel file.')
        sys.exit(1)

    file_ba1 = [[""]*20 for i in range(3000)]
    file_ba2 = [""]*20 
    file_check = [[""]*20 for i in range(3000)]

    if os.path.isfile(input_file):
        read_excel_xlsx(input_file, file_sheet, file_columns, file_start_row, file_ba1)
    else:
        print(f'{input_file} is not a file or the file does not exist.')
        sys.exit(1)

    if not os.path.isdir(input_folder):
        print(f'{input_folder} is not a folder or the folder does not exist.')
        sys.exit(1)

    file_check[0][0] = file_columns.split(",")[0].replace("\\n"," ")
    file_check[0][1] = file_columns.split(",")[1].replace("\\n"," ")
    file_check[0][2] = file_columns.split(",")[2].replace("\\n"," ")
    file_check[0][3] = file_columns.split(",")[3].replace("\\n"," ")
    file_check[0][4] = file_columns.split(",")[4].replace("\\n"," ")
    file_check[0][5] = file_columns.split(",")[5].replace("\\n"," ")
    file_check[0][6] = file_columns.split(",")[6].replace("\\n"," ")
    file_check[0][7] = file_columns.split(",")[7].replace("\\n"," ")
    file_check[0][8] = "Changed"
    file_check[0][9] = file_columns.split(",")[9].replace("\\n"," ")
    file_check[0][10] = file_columns.split(",")[10].replace("\\n"," ")
    file_check[0][11] = date_columns.split(",")[7].replace("\,",",")
    file_check[0][12] = date_columns.split(",")[7].replace("\,",",")
    file_check[0][13] = "filename"

    icheck = 1
    for ba1 in file_ba1:
        if ba1[4] and ba1[4] != "(NC)":
            file_check[icheck][0] = str(ba1[0]).strip()
            file_check[icheck][1] = str(ba1[1]).strip()
            file_check[icheck][2] = str(ba1[2]).strip()
            file_check[icheck][3] = str(ba1[3]).strip()
            file_check[icheck][4] = str(ba1[4]).strip()
            file_check[icheck][5] = str(ba1[5]).strip()
            file_check[icheck][6] = str(ba1[6]).strip()
            file_check[icheck][7] = str(ba1[7]).strip()
            file_check[icheck][8] = "SPEC1"
            file_check[icheck][9] = str(ba1[9]).strip()
            file_check[icheck][10] = str(ba1[10]).strip()

            check_path = ba1[4]+"-"+ba1[8]+".xls"
            check_file_path = os.path.join(input_folder, ba1[0], check_path)
            if os.path.isfile(check_file_path):
                print(f'{check_file_path} is exist.')
                read_date_excel_xls(check_file_path, date_sheet, date_columns, date_start_row, file_ba2)
            else:
                print(f'{check_file_path} is not a file or the file does not exist.')
                check_path = ba1[4]+"-"+ba1[8]+".xlsx"
                check_file_path = os.path.join(input_folder, ba1[0], check_path)
                if os.path.isfile(check_file_path):
                    print(f'{check_file_path} is exist.')
                    try:
                        read_date_excel_xlsx(check_file_path, xlsx_sheet, date_columns, date_start_row, file_ba2)
                    except OSError as err:
                        print("OS error: {0}".format(err))
                    except ValueError:
                        print("Could not convert data to an integer.")
                    except:
                        print("Unexpected error:", sys.exc_info()[0])
                        read_date_excel_xlsx(check_file_path, date_sheet, date_columns, date_start_row, file_ba2)  
                else:
                    print(f'{check_file_path} is not a file or the file does not exist.')
                    check_path = ba1[4]+"_"+ba1[8]+".xlsx"
                    check_file_path = os.path.join(input_folder, ba1[0], check_path)
                    if os.path.isfile(check_file_path):
                        print(f'{check_file_path} is exist.')
                        read_date_excel_xlsx(check_file_path, date_sheet, date_columns, date_start_row, file_ba2)
                    else:
                        print(f'{check_file_path} is not a file or the file does not exist.')
                        icheck += 1
                        continue
            # print(file_ba2)
            # sys.exit(1)
            file_check[icheck][8] = "N"
            if file_check[icheck][0] != str(file_ba2[0]).strip():
                file_check[icheck][0] = str(file_check[icheck][0]) + "->" + str(file_ba2[0])
                file_check[icheck][8] = "Y"
            if file_check[icheck][1] != str(file_ba2[1]).strip():
                file_check[icheck][1] = str(file_check[icheck][1]) + "->" + str(file_ba2[1])
                file_check[icheck][8] = "Y"
            if file_check[icheck][2] != str(file_ba2[2]).strip():
                file_check[icheck][2] = str(file_check[icheck][2]) + "->" + str(file_ba2[2])
                file_check[icheck][8] = "Y"
            if file_check[icheck][3] != str(file_ba2[3]).strip():
                file_check[icheck][3] = str(file_check[icheck][3]) + "->" + str(file_ba2[3])
                file_check[icheck][8] = "Y"
            if file_check[icheck][4] != str(file_ba2[4]).strip():
                file_check[icheck][4] = str(file_check[icheck][4]) + "->" + str(file_ba2[4])
                file_check[icheck][8] = "Y"
            if file_check[icheck][5] != str(file_ba2[5]).strip():
                file_check[icheck][5] = str(file_check[icheck][5]) + "->" + str(file_ba2[5])
                file_check[icheck][8] = "Y"
            if file_check[icheck][6] != str(file_ba2[6]).strip():
                file_check[icheck][6] = str(file_check[icheck][6]) + "->" + str(file_ba2[6])
                file_check[icheck][8] = "Y"
            if file_check[icheck][7] != str(file_ba2[7]).strip():
                file_check[icheck][7] = str(file_check[icheck][7]) + "->" + str(file_ba2[7])
                file_check[icheck][8] = "Y"
            
            file_check[icheck][11] = file_ba2[8]
            file_check[icheck][12] = file_ba2[9]
            file_check[icheck][13] = ba1[0]+"/"+check_path
            icheck += 1    


    sheet_name="158-msd-v3.00-check"
    if os.path.isfile(output_file):
        deleteExcel(output_file, sheet_name)
        addExcel(output_file, sheet_name, file_check)
    else:
        write_excel_xlsx(output_file, sheet_name, file_check)





